package ws.jdbc.concurrent.driver.sqlserver.tds;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.net.Socket;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import ws.jdbc.concurrent._development.DevMode;
import ws.jdbc.concurrent.driver.sqlserver.tds.datatypes.VARLENTYPE;
import ws.jdbc.concurrent.driver.sqlserver.tds.rpc.ParameterData;
import ws.jdbc.concurrent.driver.sqlserver.tds.rpc.UpdateValue;
import static ws.jdbc.concurrent.driver.sqlserver.tds.rpc.StoredProcedures.*;
import ws.utils.ArrayUtil;

/**
 *
 * @author Wilson Santos, 36228 <wilson.santos@ua.pt>
 */
public class TDSMessage {

  private DataOutputStream os;
  private DataInputStream is;
  /* -------------------------- MESSAGE TYPES ---------------------------------- */
  /** Message type: SQL batch. */
  protected static final byte TYPE_SQL_BATCH = (byte) 1;
  /** Message type: RPC. */
  protected static final byte TYPE_RPC = (byte) 3;
  /** Message type: Table response. This indicates a stream that contains the server response to a client request. */
  protected static final byte TYPE_TABLE_RESPONSE = (byte) 4;
  /** Message type: Attention signal. */
  protected static final byte TYPE_ATTENTION = (byte) 6;
  /** Message type: Bulk load data. This type is used to send binary data to the server. */
  protected static final byte TYPE_BULK_LOAD_DATA = (byte) 7;
  /** Message type: Transaction manager request. */
  protected static final byte TYPE_TRANSACTION_MANAGER_REQUEST = (byte) 14;
  /** Message type: TDS7 login. */
  protected static final byte TYPE_LOGIN7 = (byte) 16;
  /** Message type: SSPI message. */
  protected static final byte TYPE_SSPI = (byte) 17;
  /** Message type: Pre-login message. */
  protected static final byte TYPE_PRE_LOGIN = (byte) 18;
  /* ----------------------- PRE-LOGIN OPTIONS ------------------------------- */
  /** Pre-Login Option Token: version of the sender. */
  final byte PL_VERSION = 0x0;
  /** Pre-Login Option Token: request encryption. */
  final byte PL_FENCRYPTION = 0x1;
  /** Pre-Login Option Token: name of SQL Server instance or just %x00. */
  final byte PL_INSTAVALIDITY = 0x2;
  /** Pre-Login Option Token: client application thread id (used for debugging purposes). */
  final byte PL_THREADID = 0x3;
  /** Pre-Login Option Token: signals end of PRELOGIN message. */
  final byte PL_TERMINATOR = (byte) 0xFF;
  /** Pre-Login Option Length: version of the sender. */
  final short PL_VERSION_LENGTH = 4;
  /** Pre-Login Option Length: request encryption. */
  final short PL_FENCRYPTION_LENGTH = 1;
  /** Pre-Login Option Length: name of SQL Server instance or just %x00. */
  final short PL_INSTAVALIDITY_LENGTH = 1;
  /** Pre-Login Option Length: client application thread id (used for debugging purposes). */
  final short PL_THREADID_LENGTH = 4;
  /* ------------------------- MESSAGE SIZES --------------------------------- */
  /** Packet header size, in number of bytes. */
  protected static final short HEADER_SIZE = 8;
  /** Required max packet size. */
  protected static short MAX_PACKET_SIZE = 4094;
  /* ------------------------- MESSAGE STATUS -------------------------------- */
  /** Message status: normal message. */
  protected static final byte STATUS_NORMAL = 0x00;
  /** Message status: end of message (EOM). The last byte in the packet is EOM. */
  protected static final byte STATUS_EOM = 0x01;
  /* -------------------------- MESSAGE FIELDS -------------------------------- */
  /** Message header field: Type. */
  private byte type;
  /** Message header field: Status. */
  private byte status;
  /** Message header field: Length. */
  private short length;
  /** Message header field: PacketId. */
  private byte packetId;

  /** Test purposes only. */
  private TDSMessage() {
  }
  private Socket sock;

  /**
   * Create a new message instance.
   *
   * <b>Pre-Conditions</b>
   * <ul>
   *  <li>sock != null
   *  <li>sock.isConnected() == {@code true}
   * </ul>
   *
   * @param sock socket connected to an SQL Server instance socket.
   *
   * @throws IOException if an error occurs while reading from the data input stream.
   */
  public TDSMessage(final Socket sock) throws IOException {
    assert sock != null && sock.isConnected();

    this.sock = sock;
    this.os = new DataOutputStream(new BufferedOutputStream(sock.getOutputStream()));
    this.is = new DataInputStream(new BufferedInputStream(sock.getInputStream()));
  }

  public void close() throws IOException {
    os.close();
    is.close();
  }

  /**
   * Send a login request to the server.
   *
   * @param hostName
   * @param userName
   * @param password
   * @param serverName
   * @param database
   *
   * @return {@code true} if successful, otherwise {@code false}.
   *
   * @throws IOException if an error occurs while reading from the data input stream.
   */
  public synchronized boolean login(String hostName, String userName, String password, String serverName, String database) throws SQLException {
    /* Login only works without the pre-login message... */

    try {
      sendLoginRequest(hostName, userName, password, serverName, database);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      if (tokens.hasLoginAck()) {
        return true;
      } else {
        return false;
      }
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Execute an SQL command or batch of SQL commands.
   *
   * <p>
   * <b>Pre-Condition</b>
   * <ul>
   *  <li>{@code sqlText} != null
   * </ul>
   * </p>
   *
   * @param sqlText SQL command or batch of SQL commands.
   * 
   * @throws IOException
   */
  public synchronized ITDSResultSet executeSQLBatch(String sqlText) throws SQLException {
    assert sqlText != null;
    try {
      sendSQLBatchRequest(sqlText);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }

      return tokens;
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Close and de-allocate the cursor.
   * All associated resources will be released.
   * 
   * @param cursor  cursor handle value generated by SQL Server and returned by 
   *                the sp_cursoropen procedure.
   */
  public synchronized void cursorClose(int cursor) throws SQLException {
    try {
      execSpCursorClose(cursor);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Open a cursor for a result set.
   *
   * @param stmt SQL statement used to populate the cursor.
   *
   * @return result set instance with the results.
   *
   * @throws SQLException if an error occurs while reading from the input stream.
   *                      Or the server sent one error token.
   */
  public synchronized ITDSResultSet cursorOpen(String stmt, int scrollopt, int ccopt) throws SQLException {
    assert stmt != null;

    try {
      execSpCursorOpen(stmt, scrollopt, ccopt);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();
      tokens.setCursor();

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }

      return tokens;
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Update a set of values in the result set.
   *
   * @param cursor  handle value generated by SQL Server and returned by sp_cursoropen.
   * @param rownum   row to update.
   * @param updateRow list with the update values.
   *
   * @throws SQLException if an error occurs while reading from the input stream.
   *                      Or the server sent one error token.
   */
  public synchronized void cursorUpdate(final int cursor, final int rownum, final UpdateValue[] updateValues) throws SQLException {
    assert updateValues != null;

    try {
      execSpCursor(cursor, SP_CURSOR_OPTYPE_SETPOSITION | SP_CURSOR_OPTYPE_UPDATE, rownum, "", updateValues);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Delete row from the result set.
   *
   * @param cursor  handle value generated by SQL Server and returned by sp_cursoropen.
   * @param rownum  row to delete.
   */
  public synchronized void cursorDelete(final int cursor, final int rownum) throws SQLException {
    try {
      execSpCursor(cursor, SP_CURSOR_OPTYPE_DELETE, rownum, "");

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      System.out.println(tokens);

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Insert a set of values in the result set.
   *
   * @param cursor        handle value generated by SQL Server and returned by sp_cursoropen.
   * @param updateRow  list with the update values.
   *
   * @throws SQLException if an error occurs while reading from the input stream.
   *                      Or the server sent one error token.
   */
  public synchronized void cursorInsert(final int cursor, final UpdateValue[] updateValues) throws SQLException {
    assert updateValues != null;

    try {
      execSpCursor(cursor, SP_CURSOR_OPTYPE_INSERT, 0, "", updateValues);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Fetch a buffer of rows from the database.
   * 
   * @param cursor handle value generated by SQL Server and returned by sp_cursoropen.
   * @param fetchType specifies which cursor buffer to fetch.
   * @param rownum row offset.
   * @param nrows number of rows to fetch.
   *
   * @return result set instance with the results.
   *
   * @throws SQLException if an error occurs while reading from the input stream.
   *                      Or the server sent one error token.
   */
  public synchronized ITDSResultSet cursorFetch(final int cursor, final int fetchType, final int rownum, final int nrows) throws SQLException {
    try {
      execSpCursorFetch(cursor, fetchType, rownum, nrows);

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();
      tokens.setCursor(cursor);

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }

      return tokens;
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  /**
   * Fetch the total number of rows on the result set.
   *
   * @return number of rows on the result set.
   *
   * @throws SQLException if an error occurs while reading from the input stream.
   *                      Or the server sent one error token.
   */
  public synchronized int cursorRows() throws SQLException {
    try {
      sendSQLBatchRequest("SELECT @@CURSOR_ROWS");

      TokenCollection tokens = TokenReader.readTokens(readResponse());
      tokens.throwExceptionIfErrorExists();

      if (DevMode.concurrent_driver_sqlserver_tds) {
        System.out.println(tokens);
      }

      return tokens.getInt(0, 0);
    } catch (IOException ex) {
      throw new SQLException(ex);
    }
  }

  private ByteBuffer readResponse() throws IOException {
    final ByteBuffer ret;
    final LinkedList<byte[]> packets = new LinkedList<byte[]>();
    int bufferSize = 0, packetSize = 0;

    for (;;) {
      readResponseHeader();
      packetSize = length - HEADER_SIZE;
      packets.add(readResponseData(packetSize));
      bufferSize += packetSize;

      if (isEOM()) {
        break;
      }
    }

    ret = ByteBuffer.allocate(4 + bufferSize).
            order(ByteOrder.LITTLE_ENDIAN).
            putInt(bufferSize);
    for (byte[] packet : packets) {
      ret.put(packet);
    }
    ret.position(0);
    return ret;
  }

  private void readResponseHeader() throws IOException {
    type = is.readByte();
    status = is.readByte();
    length = is.readShort();
    is.skipBytes(2); // SPID - don't care
    packetId = is.readByte();
    is.skipBytes(1);  // Window - don't care
  }

  /**
   * Read an amount of bytes from the input stream.
   *
   * @param bufferSize amount of bytes to read.
   *
   * @return buffer with the read bytes.
   *
   * @throws IOException if an error occurs while reading from the input stream.
   */
  private byte[] readResponseData(int bufferSize) throws IOException {
    byte[] ret = new byte[bufferSize];
    is.readFully(ret);
    return ret;
  }

  /**
   * Check if the packet received is the last packet of the message.
   *
   * @return {@code true} if is end-of-message, otherwise {@code false}.
   */
  private boolean isEOM() {
    return status == 0x01;
  }

  private void send(byte messageType, byte[] stream) throws IOException {
    final short maxBytesPerPacket = (short) (MAX_PACKET_SIZE - HEADER_SIZE);
    int dataPos = 0, dataLeft = stream.length;
    byte packetCount = 1;

    while (dataLeft + HEADER_SIZE > MAX_PACKET_SIZE) {
      /* Write header. */
      writeHeader(messageType, STATUS_NORMAL, MAX_PACKET_SIZE, packetCount);
      /* Write packet data. */
      os.write(stream, dataPos, maxBytesPerPacket);
      packetCount = (byte) incModulo(packetCount, 256);

      dataPos += maxBytesPerPacket;
      dataLeft -= maxBytesPerPacket;

      /* Send packet. */
      os.flush();
    }
    /* Write header. */
    writeHeader(messageType, STATUS_EOM, dataLeft, packetCount);
    /* Write packet data. */
    os.write(stream, dataPos, dataLeft);
    /* Send packet. */
    os.flush();
  }

  private static int incModulo(int val, int modulo) {
    return val == modulo - 1 ? 0 : val + 1;
  }

  private void sendPreLogin() throws IOException {
    /* Write header. */
    writeHeader(TYPE_PRE_LOGIN, STATUS_EOM, 31, (byte) 1);

    short offset = 21; // position where the packet data begins.

    /* Write options definition. */
    // Version.
    writePreLoginToken(PL_VERSION, offset, PL_VERSION_LENGTH);
    offset += PL_VERSION_LENGTH;
    // Encryption.
    writePreLoginToken(PL_FENCRYPTION, offset, PL_FENCRYPTION_LENGTH);
    offset += PL_FENCRYPTION_LENGTH;
    // SQL Server instance.
    writePreLoginToken(PL_INSTAVALIDITY, offset, PL_INSTAVALIDITY_LENGTH);
    offset += PL_INSTAVALIDITY_LENGTH;
    // Thread Id.
    writePreLoginToken(PL_THREADID, offset, PL_THREADID_LENGTH);
    // Terminator.
    os.writeByte(PL_TERMINATOR);

    /* Write options data. */
    // Version.
    os.writeInt(0);
    // Encryption.
    os.writeByte(0x0);
    // SQL Server instance.
    os.writeByte(0x0);
    // Thread Id.
    os.writeInt(0);

    os.flush();
  }

  private void sendLoginRequest(String hostName, String userName, String password, String serverName, String database) throws IOException {
    // <editor-fold defaultstate="collapsed" desc="Pre-Conditions">
    assert hostName != null && userName != null && password != null && serverName != null && database != null;
    // </editor-fold>
    final String libraryName = "ODBC", appName = "OSQL-32";

    // size (in bytes) of the packet's data
    int packetLength = 94 + 2 // 94 == the number of bytes after the
            * (hostName.length() //    packet's header and the begining
            + userName.length() //    of "Data" stream parameter.
            + password.length()
            + appName.length()
            + serverName.length()
            + libraryName.length()
            + database.length());
    // position where the real login data begins.
    short offset = 94;

    /* Write header. */
    writeHeader(TYPE_LOGIN7, STATUS_EOM, packetLength, (byte) 1);

    /* Write packet data. */
    // <editor-fold defaultstate="collapsed" desc="Login data stream">
    // Length (4 byte)
    os.write(ArrayUtil.toLittleEndian(packetLength));
    // TDSVersion (4 byte)
    os.write(new byte[]{0x03, 0x00, 0x0A, 0x73});
    // PacketSize (4 byte)
    os.write(ArrayUtil.toLittleEndian(4096));
    // ClientProgVer (4 byte)
    os.writeInt(7);
    // ClientPID (4 byte)
    os.write(new byte[]{0x00, 0x01, 0x00, 0x00});
    // ConnectionID (4 byte)
    os.writeInt(0);
    // OptionFlags1
    os.writeByte(0xe0);
    // OptionFlags2
    os.writeByte(0x03);
    // TypeFlags
    os.writeByte(0x00);
    // (FRESERVEDBYTE / OptionFlags3)
    os.writeByte(0);
    // ClientTimeZone
    os.write(new byte[]{(byte) 0xE0, 0x01, 0x00, 0x00});
    // ClientLCID
    os.write(new byte[]{0x09, 0x04, 0x00, 0x00}); // copied from example
    // OffsetLength
    //    ibHostname
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchHostName
    os.write(ArrayUtil.toLittleEndian((byte) hostName.length()));
    //    ibUserName
    offset += 2 * hostName.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchUserName
    os.write(ArrayUtil.toLittleEndian((byte) userName.length()));
    //    ibPassword
    offset += 2 * userName.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchPassword
    os.write(ArrayUtil.toLittleEndian((byte) password.length()));
    //    ibAppName
    offset += 2 * password.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchAppName
    os.write(ArrayUtil.toLittleEndian((byte) appName.length()));
    //    ibServerName
    offset += 2 * appName.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchServerName
    os.write(ArrayUtil.toLittleEndian((byte) serverName.length()));
    //    ibUnused
    offset += 2 * serverName.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchUnused
    os.writeShort(0);
    //    ibCltIntName
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchCltIntName
    os.write(ArrayUtil.toLittleEndian((byte) libraryName.length()));
    //    ibLanguage
    offset += 2 * libraryName.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchLanguage
    os.writeShort(0);
    //    ibDatabase
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchDatabase
    os.write(ArrayUtil.toLittleEndian((byte) database.length()));
    //    ClientID
    os.write(new byte[]{0x00, 0x50, (byte) 0x8B, (byte) 0xE2, (byte) 0xB7, (byte) 0x8F});
    //    ibSSPI
    offset += 2 * database.length();
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cbSSPI
    os.writeShort(0);
    //    ibAtchDBFile
    os.write(ArrayUtil.toLittleEndian(offset));
    //    cchAtchDBFile
    os.writeShort(0);
    //    [ibChangePassword]
    os.write(ArrayUtil.toLittleEndian(offset));
    //    [cchChangePassword]
    os.writeShort(0);
    //    [cbSSPILong]
    os.writeInt(0);
    // Data
    os.write(ArrayUtil.toLittleEndian(hostName));
    os.write(ArrayUtil.toLittleEndian(userName));
    os.write(passWordEncrypt(ArrayUtil.toLittleEndian(password)));
    os.write(ArrayUtil.toLittleEndian(appName));
    os.write(ArrayUtil.toLittleEndian(serverName));
    os.write(ArrayUtil.toLittleEndian(libraryName));
    os.write(ArrayUtil.toLittleEndian(database));
    // </editor-fold>

    os.flush();
  }

  private void sendSQLBatchRequest(String sqlText) throws IOException {
    final int allHeaderSize = 22; // 2 * DWORD + USHORT + DWORD + ULONGLONG
    final byte[] query = ArrayUtil.toLittleEndian(sqlText);
    ByteBuffer stream = ByteBuffer.allocate(allHeaderSize + query.length).order(ByteOrder.LITTLE_ENDIAN);
    /*  ALL_HEADERS */
    //    TotalLength
    stream.putInt(allHeaderSize);
    //    HeaderLength
    stream.putInt(18);  // allHeaderSize - DWORD
    //    HeaderType
    stream.putShort((short) 0x0002);  // Transaction Descriptor
    //    HeaderData
    //      TransactionDescriptor
    stream.putLong(0);
    //      OutstandingRequestCount
    stream.putInt(1);

    /* SQL Text */
    stream.put(query);

    this.send(TYPE_SQL_BATCH, stream.array());
  }

  /**
   * Call the 'sp_cursorclose' RPC.
   * This RPC closes and de-allocates the cursor.
   * 
   * @param cursor  cursor handle value.
   */
  private void execSpCursorClose(int cursor) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(1);

    // cursor
    list.add(new ParameterData((byte) 0, cursor));

    sendRPCRequest(SP_CURSORCLOSE, list);
  }

  private void execSpCursorOpen(String stmt, int scrollopt, int ccopt) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(5);

    // cursor
    list.add(new ParameterData((byte) 1, 0));
    // stmt
    list.add(new ParameterData((byte) 0, VARLENTYPE.NVARCHARTYPE, 8000, stmt, COLLATION.getBytes()));
    // scrollopt
    list.add(new ParameterData((byte) 1, scrollopt));
    // ccopt
    list.add(new ParameterData((byte) 1, ccopt));
    // rowcount
    list.add(new ParameterData((byte) 1, 0));

    sendRPCRequest(SP_CURSOROPEN, list);
  }

  private void execSpCursorFetch(int cursor, int fetchType, int rownum, int nrows) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(4);

    // cursor
    list.add(new ParameterData((byte) 0, cursor));
    // fetchtype
    list.add(new ParameterData((byte) 0, fetchType));
    // rownum
    list.add(new ParameterData((byte) 0, rownum));
    // nrows
    list.add(new ParameterData((byte) 0, nrows));

    sendRPCRequest(SP_CURSORFETCH, list);
  }

  private void execSpCursorFetchInfo(int cursor) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(4);

    // cursor
    list.add(new ParameterData((byte) 0, cursor));
    // fetchtype
    list.add(new ParameterData((byte) 0, SP_CURSORFETCH_FETCHTYPE_INFO));
    // rownum
    list.add(new ParameterData((byte) 1, 0));
    // nrows
    list.add(new ParameterData((byte) 1, 0));

    sendRPCRequest(SP_CURSORFETCH, list);
  }

  /**
   * Request positioned updates.
   *
   * <p>
   * This procedure performs operations on one or more row within a cursor's
   * fetch buffer.
   * </p>
   *
   * @param cursor  the cursor handle (value previously generated by SQL Server).
   * @param optype  operation the cursor will perform.
   * @param rownum  row in fetch buffer which the cursor will operate on.
   * @param table   table name that identifies the table that optype applies to
   *                when the cursor definition involves a join or ambiguous
   *                column names are returned by the value parameter.
   * @param values  list with the update values.
   * 
   * @throws IOException
   */
  private void execSpCursor(int cursor, int optype, int rownum, String table, UpdateValue[] values) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(4 + values.length);

    // cursor
    list.add(new ParameterData((byte) 0, cursor));
    // optype
    list.add(new ParameterData((byte) 0, optype));
    // rownum
    list.add(new ParameterData((byte) 0, rownum));
    // table
    list.add(new ParameterData((byte) 0, VARLENTYPE.NVARCHARTYPE, 8000, table, COLLATION.getBytes()));
    // value
    list.addAll(Arrays.asList(values));

    sendRPCRequest(SP_CURSOR, list);
  }

  private void execSpCursor(int cursor, int optype, int rownum, String table) throws IOException {
    ArrayList<ParameterData> list = new ArrayList<ParameterData>(4);

    // cursor
    list.add(new ParameterData((byte) 0, cursor));
    // optype
    list.add(new ParameterData((byte) 0, optype));
    // rownum
    list.add(new ParameterData((byte) 0, rownum));
    // table
    list.add(new ParameterData((byte) 0, VARLENTYPE.NVARCHARTYPE, 8000, table, COLLATION.getBytes()));

    sendRPCRequest(SP_CURSOR, list);
  }

  private void sendRPCRequest(short procId, List<ParameterData> parameterList) throws IOException {
    final int allHeaderSize = 22; // 2 * DWORD + USHORT + DWORD + ULONGLONG

    int parameterSize = 0;
    for (ParameterData p : parameterList) {
      parameterSize += p.size();
    }

    ByteBuffer stream = ByteBuffer.allocate(allHeaderSize + 3 * 2
            + parameterSize).order(ByteOrder.LITTLE_ENDIAN);

    // ALL_HEADERS
    //    TotalLength
    stream.putInt(allHeaderSize);
    //    HeaderLength
    stream.putInt(18);  // allHeaderSize - DWORD
    //    HeaderType
    stream.putShort((short) 0x0002);  // Transaction Descriptor
    //    HeaderData
    //      TransactionDescriptor
    stream.putLong(0);
    //      OutstandingRequestCount
    stream.putInt(1);

    // RPCReqBatch
    //  NameLenProcID
    //    ProcIDSwitch
    stream.putShort((short) 0xFFFF);
    //    ProcID
    stream.putShort(procId);
    //  OptionFlags (SHORT)
    stream.putShort((short) 0x0);
    //  *ParameterData

    for (ParameterData p : parameterList) {
      stream.put(p.getBytes());
    }

    this.send(TYPE_RPC, stream.array());
  }

  /**
   * <i>Quote section 2.2.6.3:</i>
   *
   * <p>
   * Before submitting a password from the client to the server, for every byte in the password buffer
   * starting with the position pointed to by IbPassword, the client SHOULD first swap the four high bits
   * with the four low bits and then do a bit-XOR with 0xA5 (10100101). After reading a submitted
   * password, for every byte in the password buffer starting with the position pointed to by IbPassword,
   * the server SHOULD first do a bit-XOR with 0xA5 (10100101) and then swap the four high bits with
   * the four low bits.
   * </p>
   *
   * @param pwd Password (unicode / little-endian).
   *
   * @return Password transformed with the steps described above.
   */
  private static byte[] passWordEncrypt(byte[] pwd) {
    for (int i = 0; i < pwd.length; ++i) {
      pwd[i] = (byte) (swapBits(pwd[i]) ^ 0xA5);
    }
    return pwd;
  }

  /**
   * Swap the 4 low bits with the 4 high bits.
   *
   * @param byteVal Value.
   *
   * @return Value with bits swapped.
   */
  private static byte swapBits(byte byteVal) {
    byte high_bits = (byte) (byteVal >> 4 & 0x0F); // unsigned shift does NOT work.
    byte low_bits = (byte) (byteVal << 4);
    return (byte) (low_bits | high_bits);
  }

  /**
   * Write the packet's header to the output stream.
   *
   * @param messageType Identification of the type of message.
   * @param status Status of the message.
   * @param streamLength Number of bytes on the packet's data stream.
   * @param packetId Packet number (the packets of the same message are numbered
   *                 in an incremental manner.
   *
   * @throws IOException if an error occurs while writing to the output stream.
   */
  private void writeHeader(byte messageType, byte status, int streamLength, byte packetId) throws IOException {
    // Type
    os.writeByte(messageType);
    // Status
    os.writeByte(status);
    // Length
    os.writeShort(HEADER_SIZE + streamLength);
    // SPID
    os.writeShort(0x0000);
    // PacketId
    os.writeByte(packetId);
    // Window
    os.writeByte(0x00);
  }

  /**
   * Auxiliary function for sending an Pre-Login message.
   * It sends to the output stream one token id, the position where the data
   * related to that token is, and the length of the data.
   *
   * @param tokenId Identification of the token.
   * @param position Position where the data related to that token is.
   * @param length Length of the data.
   *
   * @throws IOException if an error occurs while writing to the output stream.
   */
  private void writePreLoginToken(byte tokenId, short position, short length) throws IOException {
    os.writeByte(tokenId);
    os.writeShort(position);
    os.writeShort(length);
  }
}
